package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Vector;

import com.peenongsaban.freelance.bean.AptitudeBean;
import com.peenongsaban.freelance.bean.PromotionBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.PostJobDAO;
import com.peenongsaban.freelance.meta.AuthorizeJobMeta;
import com.peenongsaban.freelance.meta.JobMeta;
import com.peenongsaban.freelance.meta.PaymentOfJobMeta;
import com.peenongsaban.freelance.meta.PostJobAptitudeMeta;
import com.peenongsaban.freelance.meta.PostJobMeta;
import com.peenongsaban.freelance.meta.PromotionMeta;
import com.peenongsaban.freelance.model.PostJobModel;
import com.peenongsaban.freelance.utils.ConstantUtils;
import com.peenongsaban.freelance.utils.ExceptionUtil;

public class PostJobDAOImpl implements PostJobDAO {

	@Override
	public Vector<PromotionBean> listAllPromotionOfJob() {

		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PromotionBean> vector = new Vector<PromotionBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT * FROM " + PromotionMeta.TABLE;
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			databaseConnection.freeConnection(conn);
			while (rs.next()) {
				PromotionBean bean = new PromotionBean();
				bean.setPromotionID(rs.getInt(PromotionMeta.COL_PROMOTION_ID));
				;
				bean.setPromotionName(rs
						.getString(PromotionMeta.COL_PROMOTION_NAME));
				;
				bean.setDescription(rs.getString(PromotionMeta.COL_DESCRIPTION));
				;
				bean.setServiceCharge(rs
						.getDouble(PromotionMeta.COL_SERVICE_CHARGE));
				vector.add(bean);
			}
			return vector;
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			} finally {
				databaseConnection.destroy();
				databaseConnection = null;
				try {
					conn.close();
				} catch (SQLException e) {
					ExceptionUtil.messageException(new Throwable(), e);
				}
			}
		}
		return vector;

	}

	@Override
	public void insertPostJob(PostJobModel model, String idCard) {
		// Generate ID
		Date dateNow = new Date();
		SimpleDateFormat ft = new SimpleDateFormat("ddMMyyyykkmmss");

		// Convert date to calendar
		Calendar cal = Calendar.getInstance();

		// Job

		// post_job

		// Job-Picture

		// payment_of_job

		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		try {
			// Insert job table
			String sqlInsertJob = "INSERT INTO " + JobMeta.TABLE + "("
					+ JobMeta.COL_JOB_ID + ", " + JobMeta.COL_JOB_NAME + ", "
					+ JobMeta.COL_DESCRIPTION_OF_JOB + ", "
					+ JobMeta.COL_FORMAT_TIME + ", " + JobMeta.COL_WORK_AREA
					+ ", " + JobMeta.COL_START_DATE_OF_JOB + ", "
					+ JobMeta.COL_END_DATE_OF_JOB + ") "
					+ "VALUES(?,?,?,?,?,?,?)";
			statement = conn.prepareStatement(sqlInsertJob);
			String jobId = "J" + ft.format(dateNow) + idCard;
			statement.setString(1, jobId);
			statement.setString(2, model.getPostjob().getJob().getJobName());
			statement.setString(3, model.getPostjob().getJob()
					.getDescriptionOfJob());
			statement.setString(4, model.getPostjob().getJob().getFormatTime());
			statement.setString(5, model.getPostjob().getJob().getWorkArea());
			cal.setTime(model.getPostjob().getJob().getStartDateOfJob());
			java.sql.Date startDate = new java.sql.Date(cal.getTime().getTime());
			statement.setDate(6, startDate);
			cal.setTime(model.getPostjob().getJob().getEndDateOfJob());
			java.sql.Date endDate = new java.sql.Date(cal.getTime().getTime());
			statement.setDate(7, endDate);
			statement.execute();

			// Insert post job table
			String sqlInsertPostJob = "INSERT INTO " + PostJobMeta.TABLE + "("
					+ PostJobMeta.COL_POST_ID + ", "
					+ PostJobMeta.COL_STATUS_OF_POST + ", "
					+ PostJobMeta.COL_POST_DATE + ", "
					+ PostJobMeta.COL_END_DATE_OF_POST + ", "
					+ PostJobMeta.COL_JOB_ID + ", " + PostJobMeta.COL_ID_CARD
					+ ") " + "VALUES(?,?,?,?,?,?)";
			statement = null;
			statement = conn.prepareStatement(sqlInsertPostJob);
			String postId = "S" + ft.format(dateNow) + idCard;
			statement.setString(1, postId);
			statement.setString(2,
					ConstantUtils.POST_JOB_STATUS_OF_POST_DEFAULT);
			cal.setTime(new Date());
			java.sql.Date postStartDate = new java.sql.Date(cal.getTime()
					.getTime());
			statement.setDate(3, postStartDate);
			cal.setTime(model.getPostjob().getEndDateOfPost());
			java.sql.Date postEndDate = new java.sql.Date(cal.getTime()
					.getTime());
			statement.setDate(4, postEndDate);
			statement.setString(5, jobId);
			statement.setString(6, idCard);

			// Insert authorize job table
			String sqlInsertAuthrizeJob = "INSERT INTO "
					+ AuthorizeJobMeta.TABLE + "("
					+ AuthorizeJobMeta.COL_POST_ID + ", "
					+ AuthorizeJobMeta.COL_ID_CARD + ", "
					+ AuthorizeJobMeta.COL_DATE_TIME + ","
					+ AuthorizeJobMeta.COL_STATUS_AUTHORIZE + ") "
					+ "VALUES(?,?,?,?)";
			statement = null;
			statement = conn.prepareStatement(sqlInsertAuthrizeJob);
			statement.setString(1, postId);
			statement.setString(2, idCard);
			cal.setTime(new Date());
			java.sql.Date authorizeDateTime = new java.sql.Date(cal.getTime()
					.getTime());
			statement.setDate(3, authorizeDateTime);
			statement.setString(4, ConstantUtils.STATUS_AUTHORIZE_JOB_POST);
			statement.execute();

			// Insert payment of job table
			String sqlInsertPaymentOfJob = "INSERT INTO "
					+ PaymentOfJobMeta.TABLE + "("
					+ JobMeta.COL_JOB_ID + ", "
					+ PaymentOfJobMeta.COL_PAYMENT_TYPE_ID + ", "
					+ PaymentOfJobMeta.COL_FIX_PRICE + ","
					+ PaymentOfJobMeta.COL_MIN_PRICE + ","
					+ PaymentOfJobMeta.COL_MAX_PRICE + ") "
					+ "VALUES(?,?,?,?,?)";
			statement = null;
			statement = conn.prepareStatement(sqlInsertPaymentOfJob);
			statement.setString(1, jobId);
			statement.setInt(2, model.getPostjob().getJob().getPaymentOfJob().getPaymentType().getPaymentTypeID());
			statement.setDouble(3, model.getPostjob().getJob().getPaymentOfJob().getFixPrice());
			statement.setDouble(4, model.getPostjob().getJob().getPaymentOfJob().getMinPrice());
			statement.setDouble(5, model.getPostjob().getJob().getPaymentOfJob().getMaxPrice());
			statement.execute();

			// Insert post job aptitude table
			for (AptitudeBean bean : model.getPostjob().getListAptitude()) {
				String sqlInsertPostJobAptitude = "INSERT INTO "
						+ PostJobAptitudeMeta.TABLE + "("
						+ PostJobAptitudeMeta.COL_POST_ID + ", "
						+ PostJobAptitudeMeta.COL_APTITUDE_ID + ") "
						+ "VALUES(?,?,?,?,?)";
				statement = null;
				statement = conn.prepareStatement(sqlInsertPostJobAptitude);
				statement.setString(1, postId);
				statement.setInt(2, bean.getAptitudeID());
				statement.execute();
			}
			
			databaseConnection.freeConnection(conn);
			conn.commit();

		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			} finally {
				databaseConnection.destroy();
				databaseConnection = null;
				try {
					conn.close();
				} catch (SQLException e) {
					ExceptionUtil.messageException(new Throwable(), e);
				}
			}
		}

	}
}
